-- @owner: lihongji
-- @date: 2022-07-11
-- @testpoint: WHERE exists/NOT exists的测试
-- @modified by: xiaqi14 2024/12/10 研发代码变更,修改时间精度

--step1：建表；expect：成功
drop table if exists t_ustore_todate_case0022;
create table t_ustore_todate_case0022( col_1 bigint, col_2 timestamp  without time zone, col_3 bool,col_4 decimal,col_5 text,col_6 smallint,col_7 char(30),
col_8 double precision,col_9 text,col_10 character varying(30),col_11 bool ,col_12 bytea ,col_13 real ,col_14 numeric ,col_15 blob ,col_16 integer ,col_17 int ,
col_18 timestamp  with time zone ,col_19 binary_integer ,col_20 interval day to second ,col_21 boolean, col_22 nchar(30),col_23 BINARY_INTEGER,col_24 nchar(100),
col_25 character(1000), col_26 text,col_27 float,col_28 float8,col_29 bigint,col_30 timestamp  with time zone ,col_31 timestamp ,col_32 bytea,col_33 interval year to month,
col_34 character(30),col_35 smallint,col_36 blob,col_37 char(300),col_38 float,col_39 raw(100),
col_40 clob ,col_41 binary_double,col_42 number(6,2),col_43 decimal(6,2),col_44 varchar2(50),col_45 varchar(30),col_46 nvarchar2(100),col_47 numeric(12,6),col_48 nvarchar2(30),
col_49 date,col_50 bytea ,col_51 integer,col_52 binary_double,col_53 decimal(12,6),col_54 raw(8000),col_55 clob,col_56 varchar2(8000),col_57 smalldatetime,col_58 number(12,6),
col_59 nvarchar2(4000),col_60 raw(2000) ,col_61 blob,col_62 smalldatetime,col_63 blob,col_64 varchar(1000),col_65 date,constraint to_date_seq_001 primary key(col_1)
) with(storage_type=ustore);
--step2：创建序列；expect：成功
drop sequence if exists to_date_seq;
create sequence to_date_seq increment by 1 start with 100000;
--step3：清空数据；expect：成功
truncate table t_ustore_todate_case0022;
--step4：插入数据；expect：成功
begin
 for i in 1..1000 loop
      insert into t_ustore_todate_case0022 values(
   to_date_seq.nextval,to_timestamp ('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),true,3.1415926+to_date_seq.nextval,lpad('abc','5000','a@123&^%djgk'),i,lpad('abc','30','@'),i+1.456789445455,lpad('abc','5000','a@123&^%djgk'),lpad('abc','30','b'),
   false,lpad('10','5000','0101')::bytea,3.1415926+to_date_seq.nextval,i/4,lpad('10','8000','01010')::blob,i,i,to_timestamp ('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),'1',(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),
   0,rpad('abc','30','e'),to_date_seq.nextval,rpad('abc','100','exc'),lpad('abc','1000','a@123&^%djgk'),lpad('abc','5000','a@123&^%djgk'),i/4,to_date_seq.nextval-99,i*3.1415,to_timestamp ('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
   to_timestamp ('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),lpad('abc','5000','010')::bytea,(INTERVAL '12' YEAR),rpad('abc','30','&'),i,lpad('10',5000,'01010')::blob,rpad('abc','300','exc'),i/2.15,rpad('0F',100,'AADB9')::raw,lpad('abc','5000','a@123&^%djgk'),
   1.0E+100,3.14+i,i+445.255,rpad('abc','30','&'),lpad('abc','30','&'),rpad('abc','100','&GDsh'),125563.141592,rpad('abc','30','e'),to_timestamp ('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),lpad('abc','5000','21')::bytea,
   to_date_seq.nextval+2,-1.79E+100,98*0.99,lpad('10','8000','01010')::raw ,lpad('abc','5000','a@123&^%djgk'),rpad('abc','8000','a@123&^%djgk'),to_timestamp ('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),25563.1415,lpad('abc','3000','a字符串@123&^%djgk'),lpad('10',2000,'01010')::raw,
   lpad('10','200','01010')::blob,to_timestamp ('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),'010101111111100000000000000',rpad('abc','1000','&GDsh'),to_timestamp ('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF')
   );
    end loop;
end;
/
-- step5:WHERE exists/NOT exists的测试;expect:成功
select  count(*),to_date(to_char(col_65)),to_date(to_char(col_49)) from t_ustore_todate_case0022 where exists (select to_date(to_char(col_65)) from t_ustore_todate_case0022) group by to_date(to_char(col_65)),to_date(to_char(col_49))
having  extract(year from to_date(to_char(col_65)))>'2018' and extract(year from to_date(to_char(col_49)))<'2021'
order by to_date(to_char(col_65)),to_date(to_char(col_49));
select  count(*),to_date(to_char(col_65)),to_date(to_char(col_49)) from t_ustore_todate_case0022 where  exists (select to_date(to_char(col_65)) from t_ustore_todate_case0022 where extract(year from to_date(to_char(col_65)))>'2019') group by to_date(to_char(col_65)),to_date(to_char(col_49))
having  extract(year from to_date(to_char(col_65)))>'2018' and extract(year from to_date(to_char(col_49)))<'2021'
order by to_date(to_char(col_65)),to_date(to_char(col_49));
--step6:清理环境；expect：成功
drop sequence if exists to_date_seq;
drop table if exists t_ustore_todate_case0022;
